﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DIT.Domain;
using System.Data;

namespace DIT.DB.DAL
{
    static class DBExtension
    {
        private const string baseInsertQuery = "insert into {0}({1}) values({2})";
        private const string baseUpdateQuery = "update {0} set {1} where {2}";
        private const string baseDeleteQuery = "delete from {0} where {1}";
        private const string baseGetAllQuery = "select * from {0}";
        private const string baseGetByIdQuery = "select * from {0} where {1}";

        #region Taxpayer
        internal static string InsertQuery(this Account account)
        {
            StringBuilder columnList = new StringBuilder();
            StringBuilder valueList = new StringBuilder();

            #region Adding Parameters
            columnList.Append("AccountId, ");
            valueList.Append(account.ID.SurroundQuote().AddComma());

            columnList.Append("AccountType, ");
            valueList.Append(((int)account.AccountType).AddComma());

            if (!string.IsNullOrWhiteSpace(account.PAN))
            {
                columnList.Append("PAN, ");
                valueList.Append(account.PAN.SurroundQuote().AddComma());
            }

            columnList.Append("TaxPayerType, ");
            valueList.Append(((int)account.TaxPayerType).AddComma());

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.FirstName))
            {
                columnList.Append("FirstName, ");
                valueList.Append(account.AccountInfo.AssesseeName.FirstName.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.MiddleName))
            {
                columnList.Append("MiddleName, ");
                valueList.Append(account.AccountInfo.AssesseeName.MiddleName.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.SurNameOrOrgName))
            {
                columnList.Append("SurNameOrOrgName, ");
                valueList.Append(account.AccountInfo.AssesseeName.SurNameOrOrgName.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.OrgOldName))
            {
                columnList.Append("OrgOldName, ");
                valueList.Append(account.AccountInfo.AssesseeName.OrgOldName.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.ResidenceNo))
            {
                columnList.Append("ResidenceNo, ");
                valueList.Append(account.AccountInfo.Address.ResidenceNo.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.ResidenceName))
            {
                columnList.Append("ResidenceName, ");
                valueList.Append(account.AccountInfo.Address.ResidenceName.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.RoadOrStreet))
            {
                columnList.Append("RoadOrStreet, ");
                valueList.Append(account.AccountInfo.Address.RoadOrStreet.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.LocalityOrArea))
            {
                columnList.Append("LocalityOrArea, ");
                valueList.Append(account.AccountInfo.Address.LocalityOrArea.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.CityOrTownOrDistrict))
            {
                columnList.Append("CityOrTownOrDistrict, ");
                valueList.Append(account.AccountInfo.Address.CityOrTownOrDistrict.SurroundQuote().AddComma());
            }

            if (account.AccountInfo.Address.StateCode.HasValue)
            {
                columnList.Append("StateCode, ");
                valueList.Append(((int)account.AccountInfo.Address.StateCode.Value).AddComma());
            }

            if (account.AccountInfo.Address.PinCode.HasValue)
            {
                columnList.Append("PinCode, ");
                valueList.Append(account.AccountInfo.Address.PinCode.Value.SurroundQuote().AddComma());
            }

            if (account.AccountInfo.MobileNo.HasValue)
            {
                columnList.Append("MobileNo, ");
                valueList.Append(account.AccountInfo.MobileNo.Value.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(account.AccountInfo.EmailAddress))
            {
                columnList.Append("EmailAddress, ");
                valueList.Append(account.AccountInfo.EmailAddress.SurroundQuote().AddComma());
            }

            if (account.AccountInfo.Address.STDCode.HasValue)
            {
                columnList.Append("STDcode, ");
                valueList.Append(account.AccountInfo.Address.STDCode.Value.SurroundQuote().AddComma());
            }

            if (account.AccountInfo.Address.PhoneNo.HasValue)
            {
                columnList.Append("PhoneNo, ");
                valueList.Append(account.AccountInfo.Address.PhoneNo.Value.SurroundQuote().AddComma());
            }

            if (account.AccountInfo.EmployerCategory.HasValue)
            {
                columnList.Append("EmployerCategory, ");
                valueList.Append(((int)account.AccountInfo.EmployerCategory.Value).AddComma());
            }

            if (account.AccountInfo.Gender.HasValue)
            {
                columnList.Append("Gender, ");
                valueList.Append(((int)account.AccountInfo.Gender.Value).AddComma());
            }

            if (account.AccountInfo.Status.HasValue)
            {
                columnList.Append("Status, ");
                valueList.Append(((int)account.AccountInfo.Status.Value).AddComma());
            }

            if (account.AccountInfo.DOB.HasValue)
            {
                columnList.Append("DOB");
                valueList.Append(account.AccountInfo.DOB.Value.SurroundQuote());
            }
            #endregion
            return string.Format(baseInsertQuery, "Account", columnList.ToString().Trim().TrimEnd(','), valueList.ToString().Trim().TrimEnd(','));
        }
        internal static string ModifyQuery(this Account account)
        {
            StringBuilder pairList = new StringBuilder();

            #region Adding Parameters

            pairList.Append("PAN=");
            if (!string.IsNullOrWhiteSpace(account.PAN))
                pairList.Append(account.PAN.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("TaxPayerType=");
            pairList.Append(((int)account.TaxPayerType).AddComma());

            pairList.Append("FirstName=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.FirstName))
                pairList.Append(account.AccountInfo.AssesseeName.FirstName.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("MiddleName=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.MiddleName))
                pairList.Append(account.AccountInfo.AssesseeName.MiddleName.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("SurNameOrOrgName=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.SurNameOrOrgName))
                pairList.Append(account.AccountInfo.AssesseeName.SurNameOrOrgName.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("OrgOldName=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.AssesseeName.OrgOldName))
                pairList.Append(account.AccountInfo.AssesseeName.OrgOldName.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("ResidenceNo=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.ResidenceNo))
                pairList.Append(account.AccountInfo.Address.ResidenceNo.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("ResidenceName=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.ResidenceName))
                pairList.Append(account.AccountInfo.Address.ResidenceName.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("RoadOrStreet=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.RoadOrStreet))
                pairList.Append(account.AccountInfo.Address.RoadOrStreet.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("LocalityOrArea=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.LocalityOrArea))
                pairList.Append(account.AccountInfo.Address.LocalityOrArea.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("CityOrTownOrDistrict=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.Address.CityOrTownOrDistrict))
                pairList.Append(account.AccountInfo.Address.CityOrTownOrDistrict.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("StateCode=");
            if (account.AccountInfo.Address.StateCode.HasValue)
                pairList.Append(((int)account.AccountInfo.Address.StateCode.Value).AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("PinCode=");
            if (account.AccountInfo.Address.PinCode.HasValue)
                pairList.Append(account.AccountInfo.Address.PinCode.Value.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("MobileNo=");
            if (account.AccountInfo.MobileNo.HasValue)
                pairList.Append(account.AccountInfo.MobileNo.Value.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("EmailAddress=");
            if (!string.IsNullOrWhiteSpace(account.AccountInfo.EmailAddress))
                pairList.Append(account.AccountInfo.EmailAddress.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("STDcode=");
            if (account.AccountInfo.Address.STDCode.HasValue)
                pairList.Append(account.AccountInfo.Address.STDCode.Value.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("PhoneNo=");
            if (account.AccountInfo.Address.PhoneNo.HasValue)
                pairList.Append(account.AccountInfo.Address.PhoneNo.Value.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("EmployerCategory=");
            if (account.AccountInfo.EmployerCategory.HasValue)
                pairList.Append(((int)account.AccountInfo.EmployerCategory.Value).AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("Gender=");
            if (account.AccountInfo.Gender.HasValue)
                pairList.Append(((int)account.AccountInfo.Gender.Value).AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("Status=");
            if (account.AccountInfo.Status.HasValue)
                pairList.Append(((int)account.AccountInfo.Status.Value).AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("DOB=");
            if (account.AccountInfo.DOB.HasValue)
                pairList.Append(account.AccountInfo.DOB.Value.SurroundQuote());
            else
                pairList.Append("NULL".AddComma());
            #endregion

            return string.Format(baseUpdateQuery, "Account", pairList.ToString().Trim().TrimEnd(','), "AccountId=" + account.ID.SurroundQuote());
        }
        internal static string AccountDeleteQuery(Guid entityId)
        {
            return string.Format(baseDeleteQuery, "Account", "AccountId=" + entityId.SurroundQuote());
        }
        internal static string AccountGetAllQuery()
        {
            return string.Format(baseGetAllQuery, "Account");
        }
        internal static string AccountGetByIdQuery(Guid entityId)
        {
            return string.Format(baseGetByIdQuery, "Account", "AccountId=" + entityId.SurroundQuote());
        }
        internal static object MapAccount(DataRow row)
        {
            Account taxPayer;

            Guid accountId = new Guid(Convert.ToString(row["AccountId"]));
            object TaxPayerType = row["TaxPayerType"];
            TaxPayerTypeENUM taxPayerType = (TaxPayerTypeENUM)Enum.Parse(typeof(TaxPayerTypeENUM), TaxPayerType + string.Empty);

            switch (taxPayerType)
            {
                case TaxPayerTypeENUM.Firm:
                    taxPayer = new FirmAccount(row);
                    break;
                case TaxPayerTypeENUM.HUF:
                    taxPayer = new HUFAccount(row);
                    break;
                case TaxPayerTypeENUM.Individual:
                    taxPayer = new IndividualAccount(row);
                    break;
                default:
                    taxPayer = null;
                    break;
            }
            taxPayer.ID = accountId;
            return taxPayer;
        }
        #endregion

        #region FilingStatus
        internal static string InsertQuery(this FilingStatus filingStatus)
        {
            StringBuilder columnList = new StringBuilder();
            StringBuilder valueList = new StringBuilder();

            #region Adding Parameters
            columnList.Append("FilingStatusId, ");
            valueList.Append(filingStatus.ID.SurroundQuote().AddComma());

            columnList.Append("AccountId, ");
            valueList.Append(filingStatus.AccountId.SurroundQuote().AddComma());

            columnList.Append("AssessmentYear, ");
            valueList.Append(filingStatus.AssessmentYear.SurroundQuote().AddComma());

            if (!string.IsNullOrWhiteSpace(filingStatus.DesigOfficerWardorCircle))
            {
                columnList.Append("DesigOfficerWardorCircle, ");
                valueList.Append(filingStatus.DesigOfficerWardorCircle.SurroundQuote().AddComma());
            }

            if (filingStatus.ReturnFileSection.HasValue)
            {
                columnList.Append("ReturnFileSection, ");
                valueList.Append(((ulong)filingStatus.ReturnFileSection.Value).SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(filingStatus.AckNoOriginalReturn))
            {
                columnList.Append("AckNoOriginalReturn, ");
                valueList.Append(filingStatus.AckNoOriginalReturn.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(filingStatus.NoticeNo))
            {
                columnList.Append("NoticeNo, ");
                valueList.Append(filingStatus.NoticeNo.SurroundQuote().AddComma());
            }

            if (filingStatus.NoticeDate.HasValue)
            {
                columnList.Append("NoticeDate, ");
                valueList.Append(filingStatus.NoticeDate.Value.SurroundQuote().AddComma());
            }

            if (filingStatus.ReturnType.HasValue)
            {
                columnList.Append("ReturnType, ");
                valueList.Append(filingStatus.ReturnType.Value.ToString().SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(filingStatus.RecieptNo))
            {
                columnList.Append("RecieptNo, ");
                valueList.Append(filingStatus.RecieptNo.SurroundQuote().AddComma());
            }

            if (filingStatus.OrigReturnFiledDate.HasValue)
            {
                columnList.Append("OrigReturnFiledDate, ");
                valueList.Append(filingStatus.OrigReturnFiledDate.Value.SurroundQuote().AddComma());
            }

            if (filingStatus.ResidentialStatus.HasValue)
            {
                columnList.Append("ResidentialStatus, ");
                valueList.Append(filingStatus.ResidentialStatus.Value.ToString().SurroundQuote().AddComma());
            }

            if (filingStatus.TaxStatus.HasValue)
            {
                columnList.Append("TaxStatus, ");
                valueList.Append(filingStatus.TaxStatus.Value.ToString().SurroundQuote().AddComma());
            }

            if (filingStatus.AsseseeRepFlag.HasValue)
            {
                columnList.Append("AsseseeRepFlag, ");
                valueList.Append(filingStatus.AsseseeRepFlag.Value.ToString().SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(filingStatus.AsseseeRepName))
            {
                columnList.Append("AsseseeRepName, ");
                valueList.Append(filingStatus.AsseseeRepName.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(filingStatus.AsseseeRepAddress))
            {
                columnList.Append("AsseseeRepAddress, ");
                valueList.Append(filingStatus.AsseseeRepAddress.SurroundQuote().AddComma());
            }

            if (!string.IsNullOrWhiteSpace(filingStatus.AsseseeRepPAN))
            {
                columnList.Append("AsseseeRepPAN");
                valueList.Append(filingStatus.AsseseeRepPAN.SurroundQuote());
            }
            #endregion
            return string.Format(baseInsertQuery, "FilingStatus", columnList.ToString().Trim().TrimEnd(','), valueList.ToString().Trim().TrimEnd(','));
        }
        internal static string ModifyQuery(this FilingStatus filingStatus)
        {
            StringBuilder pairList = new StringBuilder();

            #region Adding Parameters

            pairList.Append("DesigOfficerWardorCircle=");
            if (!string.IsNullOrWhiteSpace(filingStatus.DesigOfficerWardorCircle))
                pairList.Append(filingStatus.DesigOfficerWardorCircle.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("ReturnFileSection=");
            if (filingStatus.ReturnFileSection.HasValue)
                pairList.Append(((ulong)filingStatus.ReturnFileSection.Value).SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("AckNoOriginalReturn=");
            if (!string.IsNullOrWhiteSpace(filingStatus.AckNoOriginalReturn))
                pairList.Append(filingStatus.AckNoOriginalReturn.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("NoticeNo=");
            if (!string.IsNullOrWhiteSpace(filingStatus.NoticeNo))
                pairList.Append(filingStatus.NoticeNo.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("NoticeDate=");
            if (filingStatus.NoticeDate.HasValue)
                pairList.Append(filingStatus.NoticeDate.Value.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("ReturnType=");
            if (filingStatus.ReturnType.HasValue)
                pairList.Append(filingStatus.ReturnType.Value.ToString().SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("RecieptNo=");
            if (!string.IsNullOrWhiteSpace(filingStatus.RecieptNo))
                pairList.Append(filingStatus.RecieptNo.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("OrigReturnFiledDate=");
            if (filingStatus.OrigReturnFiledDate.HasValue)
                pairList.Append(filingStatus.OrigReturnFiledDate.Value.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("ResidentialStatus=");
            if (filingStatus.ResidentialStatus.HasValue)
                pairList.Append(filingStatus.ResidentialStatus.Value.ToString().SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("TaxStatus=");
            if (filingStatus.TaxStatus.HasValue)
                pairList.Append(filingStatus.TaxStatus.Value.ToString().SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("AsseseeRepFlag=");
            if (filingStatus.AsseseeRepFlag.HasValue)
                pairList.Append(filingStatus.AsseseeRepFlag.Value.ToString().SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("AsseseeRepName=");
            if (!string.IsNullOrWhiteSpace(filingStatus.AsseseeRepName))
                pairList.Append(filingStatus.AsseseeRepName.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("AsseseeRepAddress=");
            if (!string.IsNullOrWhiteSpace(filingStatus.AsseseeRepAddress))
                pairList.Append(filingStatus.AsseseeRepAddress.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());

            pairList.Append("AsseseeRepPAN=");
            if (!string.IsNullOrWhiteSpace(filingStatus.AsseseeRepPAN))
                pairList.Append(filingStatus.AsseseeRepPAN.SurroundQuote().AddComma());
            else
                pairList.Append("NULL".AddComma());
            #endregion

            return string.Format(baseUpdateQuery, "FilingStatus", pairList.ToString().Trim().TrimEnd(','), "FilingStatusId=" + filingStatus.ID.SurroundQuote());
        }
        internal static string FilingStatusDeleteQuery(Guid entityId)
        {
            return string.Format(baseDeleteQuery, "FilingStatus", "FilingStatusId=" + entityId.SurroundQuote());
        }
        internal static string FilingStatusGetAllQuery()
        {
            return string.Format(baseGetAllQuery, "FilingStatus");
        }
        internal static string FilingStatusGetByIdQuery(Guid entityId)
        {
            return string.Format(baseGetByIdQuery, "FilingStatus", "FilingStatusId=" + entityId.SurroundQuote());
        }
        internal static object MapFilingStatus(DataRow row)
        {
            return new FilingStatus(row);
        }
        #endregion
        
        #region Private Helper methods
        private static T? ConvertIfNotDBNull<T>(object o, Converter<object, T> converter) where T : struct
        {
            return o is DBNull ? (T?)null : converter(o);
        }
        private static string SurroundQuote(this string stringValue)
        {
            return "'" + stringValue + "'";
        }
        private static string SurroundQuote(this DateTime dateTimeValue)
        {
            return "'" + dateTimeValue + "'";
        }
        private static string SurroundQuote(this Guid guidValue)
        {
            return "'" + guidValue + "'";
        }
        private static string SurroundQuote(this ulong ulongValue)
        {
            return "'" + ulongValue + "'";
        }
        private static string AddComma(this Guid guidValue)
        {
            return guidValue + ",";
        }
        private static string AddComma(this string stringValue)
        {
            return stringValue + ",";
        }
        private static string AddComma(this int intValue)
        {
            return intValue + ",";
        }
        #endregion
    }
}
